/*
SELECT * FROM FN_CALCULATE_PRODUCT_COST_CODE( 888, 'ASSEMBLY' )
*/
alter FUNCTION FN_CALCULATE_PRODUCT_COST_CODE
(
	@PROCESSSTEP_ID BIGINT,
	@PS_TYPE	VARCHAR(50)
)
RETURNS @FN_RESULTS TABLE( COST_CODE VARCHAR(10) )
AS
BEGIN
	DECLARE @PIN_COUNT			VARCHAR(5)
	DECLARE @PACKAGE_TYPE			VARCHAR(5)
	DECLARE @WAFER_PART_NUMBER		VARCHAR(50)
	DECLARE @BASE_PART_NUMBER		VARCHAR(50)
	DECLARE @ASSM_PROCESSSTEP_ID	BIGINT
	DECLARE @PARENT_PRODUCT_ID		INT

	DECLARE @COST_CODE			VARCHAR(10)
	DECLARE @PIN_COST_CODE		VARCHAR(2)
	DECLARE @PACKAGE_COST_CODE	VARCHAR(2)
	DECLARE @WAFER_COST_CODE	VARCHAR(2)

	IF( @PS_TYPE = 'FINISHED GOODS' OR @PS_TYPE = 'FG' OR @PS_TYPE = 'TAPE & REEL'  OR @PS_TYPE = 'FT' OR @PS_TYPE = 'FPO'  OR @PS_TYPE = 'ASSEMBLY' )		
	BEGIN
		SELECT TOP 1 @ASSM_PROCESSSTEP_ID = PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID( @PROCESSSTEP_ID, 0) WHERE PS_TYPE = 'ASSEMBLY'	

		SELECT @PARENT_PRODUCT_ID = P_PARENT_PRODUCT_ID FROM PRODUCT WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID

		SELECT @PACKAGE_TYPE = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'PACKAGE ID' AND PROCESSSTEP_ID = @ASSM_PROCESSSTEP_ID
		SELECT @PIN_COUNT = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'PIN' AND PROCESSSTEP_ID = @ASSM_PROCESSSTEP_ID		
		SELECT TOP 1 @WAFER_PART_NUMBER = PRODUCT_NAME FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID( @ASSM_PROCESSSTEP_ID, 0) WHERE PS_TYPE = 'FAB'
		SELECT TOP 1 @BASE_PART_NUMBER = PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = @PARENT_PRODUCT_ID
	
		IF( @WAFER_PART_NUMBER IS NULL AND @BASE_PART_NUMBER IS NOT NULL )
		BEGIN
			SET @WAFER_PART_NUMBER = @BASE_PART_NUMBER
		END

		SELECT @PACKAGE_COST_CODE = COST_CODE FROM COST_CODE_MASTER WHERE COST_CODE_NO = @PACKAGE_TYPE AND COST_CODE_TYPE='PACKAGE' 
		SELECT @PIN_COST_CODE = COST_CODE FROM COST_CODE_MASTER WHERE COST_CODE_NO = @PIN_COUNT AND COST_CODE_TYPE='PIN'
		SELECT @WAFER_COST_CODE = COST_CODE FROM COST_CODE_MASTER WHERE COST_CODE_NO = @WAFER_PART_NUMBER AND COST_CODE_TYPE='WAFER'

		IF( @WAFER_COST_CODE IS NULL )
		BEGIN
			SELECT @WAFER_COST_CODE = COST_CODE FROM COST_CODE_MASTER WHERE COST_CODE_NO =  @BASE_PART_NUMBER AND COST_CODE_TYPE='WAFER' 
		END


		IF( @PACKAGE_COST_CODE IS NOT NULL AND @PIN_COST_CODE IS NOT NULL AND @WAFER_COST_CODE IS NOT NULL )
		BEGIN
			SET @COST_CODE  = ( @WAFER_COST_CODE + @PACKAGE_COST_CODE + @PIN_COST_CODE )
		END		
	END

	INSERT INTO @FN_RESULTS VALUES( @COST_CODE )

	RETURN
END
